package renthouse.dao;/*
 * @创建者：黄苟苟
 * @创建时间：2021/5/18   18:10
 * @Description描述
 **/



import renthouse.entry.Landlord;
import renthouse.util.ConnDb;

import java.sql.*;
import java.util.ArrayList;

import java.util.List;


public class LandlordDao   {
    
    public boolean insert(Landlord landlord) {
        String s="insert into landlord(name,sex,phone,numberid,loginname,password) values (?,?,?,?,?,?)";
        Connection connection= ConnDb.getConnection();
        PreparedStatement p=null;
        try {
            p=connection.prepareStatement(s);
            p.setString(1, landlord.getName());
            p.setString(2,landlord.getSex());
            p.setString(3,landlord.getPhone());
            p.setString(4,landlord.getNumberid());
            p.setString(5,landlord.getLoginname());
            p.setString(6,landlord.getPassword());

            int i=  p.executeUpdate();
            if (i>0){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally {
            ConnDb.close(connection,p,null);  //关闭
        }

        return false;
    }

/*删除文件测试方法*/
    public boolean deleteById(int i) {
        String deleteStr="delete from landlord where masterid ="+i;
        Connection connection=ConnDb.getConnection();
        Statement statement=ConnDb.getStatement(connection);
        try {
            int len=statement.executeUpdate(deleteStr);
            if (len>0){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally {
            ConnDb.close(connection,statement,null);
        }
        return false;
    }

   
    public boolean updateById(Landlord landlord) {
        if (landlord.getId()==0){
            return false;
        }
        //拼接sql语句
        String updateStr="update landlord set masterid="+landlord.getId();

        if (landlord.getName()!=null){
            updateStr+=",name='"+landlord.getName()+"'";
        }

        if (landlord.getSex()!=null){
            updateStr+=",sex='"+landlord.getSex()+"'";
        }

        if (landlord.getPhone()!=null){
            updateStr+=",phone='"+landlord.getPhone()+"'";
        }

        if (landlord.getNumberid()!=null){
            updateStr+=",numberid='"+landlord.getNumberid()+"'";
        }

        if (landlord.getLoginname()!=null){
            updateStr+=",loginname="+landlord.getLoginname();
        }

        if (landlord.getPassword()!=null){
            updateStr+=",password="+landlord.getPassword();
        }
        

        //加上条件

        updateStr+=" where masterid="+landlord.getId();


        Connection connection= ConnDb.getConnection();
        Statement statement= ConnDb.getStatement(connection);
        try {
            int len=statement.executeUpdate(updateStr);
            if (len>0){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally {
            ConnDb.close(connection,statement,null);
        }
        return false;
    }

    
    public List<Landlord> query(Landlord landlord) {

        String str="SELECT * FROM landlord where 1=1 ";
        List<Landlord> list=new ArrayList <>();
        if (landlord.getName()!=null){
            str+=" and name like concat('%','"+landlord.getName()+"','%')";
        }

        if (landlord.getSex()!=null){
            str+=" and sex like concat('%','"+landlord.getSex()+"','%')";
        }

        if (landlord.getPhone()!=null){
            str+=" and phone like concat('%','"+landlord.getPhone()+"','%')";
        }

        if (landlord.getNumberid()!=null){
            str+=" and numberid like concat('%','"+landlord.getNumberid()+"','%')";
        }

        if (landlord.getLoginname()!=null){
            str+=" and loginname like concat('%','"+landlord.getLoginname()+"','%')";
        }

        if (landlord.getPassword()!=null){
            str+=" and password like concat('%','"+landlord.getPassword()+"','%')";
        }
        

        Connection connection=ConnDb.getConnection();
        Statement statement=ConnDb.getStatement(connection);
        ResultSet resultSet=null;

        try {
            resultSet=statement.executeQuery(str);
            while (resultSet.next()){
                Landlord landlord1=new Landlord();
                landlord1.setId(resultSet.getInt("masterid"));
                landlord1.setName(resultSet.getString("name"));
                landlord1.setSex(resultSet.getString("sex"));
                landlord1.setPhone(resultSet.getString("phone"));
                landlord1.setNumberid(resultSet.getString("numberid"));
                landlord1.setLoginname(resultSet.getString("loginname"));
                landlord1.setPassword(resultSet.getString("password"));

                list.add(landlord1);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }


        return list;
    }

        public List<Landlord> queryForPage(Landlord landlord, int pageNum, int pageSize){

            int start = (pageNum - 1) * pageSize;
            String str="SELECT * FROM landlord where 1=1 ";
            List<Landlord> list=new ArrayList <>();
            if (landlord.getName()!=null){
                str+=" and name like concat('%','"+landlord.getName()+"','%')";
            }

            if (landlord.getSex()!=null){
                str+=" and sex like concat('%','"+landlord.getSex()+"','%')";
            }

            if (landlord.getPhone()!=null){
                str+=" and phone like concat('%','"+landlord.getPhone()+"','%')";
            }

            if (landlord.getNumberid()!=null){
                str+=" and numberid like concat('%','"+landlord.getNumberid()+"','%')";
            }

            if (landlord.getLoginname()!=null){
                str+=" and loginname like concat('%','"+landlord.getLoginname()+"','%')";
            }

            if (landlord.getPassword()!=null){
                str+=" and password like concat('%','"+landlord.getPassword()+"','%')";
            }

            str += " limit " + start + "," + pageSize;
            Connection connection=ConnDb.getConnection();
            Statement statement=ConnDb.getStatement(connection);
            ResultSet resultSet=null;

            try {
                resultSet=statement.executeQuery(str);
                while (resultSet.next()){
                    Landlord landlord1=new Landlord();
                    landlord1.setId(resultSet.getInt("masterid"));
                    landlord1.setName(resultSet.getString("name"));
                    landlord1.setSex(resultSet.getString("sex"));
                    landlord1.setPhone(resultSet.getString("phone"));
                    landlord1.setNumberid(resultSet.getString("numberid"));
                    landlord1.setLoginname(resultSet.getString("loginname"));
                    landlord1.setPassword(resultSet.getString("password"));

                    list.add(landlord1);
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }


            return list;
    }
    public int queryCount(Landlord landlord) {


        String str = "SELECT count(1) countNum FROM landlord where 1=1 ";
        if (landlord.getName()!=null){
            str+=" and name like concat('%','"+landlord.getName()+"','%')";
        }

        if (landlord.getSex()!=null){
            str+=" and sex like concat('%','"+landlord.getSex()+"','%')";
        }

        if (landlord.getPhone()!=null){
            str+=" and phone like concat('%','"+landlord.getPhone()+"','%')";
        }

        if (landlord.getNumberid()!=null){
            str+=" and numberid like concat('%','"+landlord.getNumberid()+"','%')";
        }

        if (landlord.getLoginname()!=null){
            str+=" and loginname like concat('%','"+landlord.getLoginname()+"','%')";
        }

        if (landlord.getPassword()!=null){
            str+=" and password like concat('%','"+landlord.getPassword()+"','%')";
        }


        Connection connection = ConnDb.getConnection();
        Statement statement = ConnDb.getStatement(connection);
        ResultSet resultSet = null;

        int count=0;
        try {
            resultSet = statement.executeQuery(str);
            while (resultSet.next()) {
                count=resultSet.getInt("countNum");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }
}
